from report import report_sxw
from report.report_sxw import rml_parse
import random
from osv import fields, osv
import time
import datetime
from dateutil.relativedelta import relativedelta
import pooler


class Parser(report_sxw.rml_parse):
    def __init__(self, cr, uid, name, context):
        super(Parser, self).__init__(cr, uid, name, context)
        self.total_year_1 = 0
        self.total_date_year = 0
        self.total_date_year_1 = 0
        self.total_date_month = 0
        self.total_date_month_1 = 0
        self.total_month_1 = 0
        self.list_cate = {}
        self.localcontext.update({
            'time': time,
            'get_company': self.get_company,
            'get_lines': self.get_lines_group_by_category_partner,
            'get_year_1': self.get_year_1,
            'get_date_year_1': self.get_date_year_1,
            'get_date_year': self.get_date_year,
            'list_cate': self.get_list_cate,
            'get_month_1': self.get_month_1,
            'get_date_month': self.get_date_month,
            'get_date_month_1': self.get_date_month_1,
            'month': self.month,
            'year': self.year,
            'date_of_year': self.date_of_year,
            'date_of_year_1': self.date_of_year_1,
            'date_of_month': self.date_of_month,
            'date_of_month_1': self.date_of_month_1,
        })
        self.context = context

    def get_company(self):
        sql = ''' select distinct(company_id) as company_id from account_invoice '''
        self.cr.execute(sql)
        list_company = self.cr.dictfetchall()
        company_obj = pooler.get_pool(self.cr.dbname).get('res.company')
        ids = []
        for data in list_company:
            ids += [data['company_id']]
        comp_obj = company_obj.browse(self.cr, self.uid, ids)
        return comp_obj

    def get_list_cate(self):
        return self.list_cate

    def get_year_1(self):
        return self.total_year_1

    def get_date_year_1(self):
        return self.total_date_year_1

    def get_date_year(self):
        return self.total_date_year

    def get_date_month_1(self):
        return self.total_date_month_1

    def get_date_month(self):
        return self.total_date_month

    def get_month_1(self):
        return self.total_month_1

    def month(self, form):
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        month = date.strftime('%m')
        return int(month)

    def year(self, form):
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        year = date.strftime('%Y')
        return int(year)

    def date_of_year(self, form):
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        year = date.strftime('%Y')
        date = time.strftime('%d/%m/%Y', time.strptime(date.strftime('%Y-%m-%d'), '%Y-%m-%d'))
        date = 'From 01/01/%s to %s' % (year, date)
        return date

    def date_of_year_1(self, form):
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        date = date - relativedelta(years=1)
        year = date.strftime('%Y')
        date = time.strftime('%d/%m/%Y', time.strptime(date.strftime('%Y-%m-%d'), '%Y-%m-%d'))
        date = 'From 01/01/%s to %s' % (year, date)
        return date

    def date_of_month(self, form):
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        year = date.strftime('%Y')
        month = date.strftime('%m')
        date = time.strftime('%d/%m/%Y', time.strptime(date.strftime('%Y-%m-%d'), '%Y-%m-%d'))
        date = 'From 01/%s/%s to %s' % (month, year, date)
        return date

    def date_of_month_1(self, form):
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        date = date - relativedelta(years=1)
        year = date.strftime('%Y')
        month = date.strftime('%m')
        date = time.strftime('%d/%m/%Y', time.strptime(date.strftime('%Y-%m-%d'), '%Y-%m-%d'))
        date = 'From 01/%s/%s to %s' % (month, year, date)
        return date

    def get_lines(self, form, company_id):
        result = []
        currency_obj = self.pool.get('res.currency')
        invoice_obj = self.pool.get('account.invoice')
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        sql = ''' select distinct(currency_id) as currency_id from account_invoice where date_part('year',date_invoice)<= %s and company_id = %s ''' % (
        date.strftime('%Y'), company_id)
        self.cr.execute(sql)
        list_currency = self.cr.dictfetchall()
        total_year_1, total_date_year, total_date_year_1, total_date_month, total_date_month_1, total_month_1 = 0, 0, 0, 0, 0, 0
        company_currency_id = self.pool.get('res.users').browse(self.cr, self.uid, self.uid,
            {}).company_id.currency_id.id
        for currency in list_currency:

            sql_year_1 = ''' select cate.id, cate.name, 0 as date_year,0 as date_year_1, sum(inv.amount_untaxed) as year_1,
                            0 as date_period,0 as date_period_1,0 as period_1, 0 as tax_date_year,0 as tax_date_year_1, sum(inv.amount_tax) as tax_year_1,
                            0 as tax_date_period,0 as tax_date_period_1,0 as tax_period_1
                            from account_invoice inv
                            inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                            inner join res_partner_category cate on (rel.category_id = cate.id)
                            where date_part('year',inv.date_invoice)= %s and inv.company_id = %s and inv.currency_id = %s 
                            and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                            ''' % (int(date.strftime('%Y')) - 1, company_id, currency['currency_id'])
            sql_date_year_1 = ''' select cate.id, cate.name, 0 as date_year, sum(inv.amount_untaxed) as date_year_1, 0 as year_1,
                        0 as date_period,0 as date_period_1,0 as period_1, 0 as tax_date_year, sum(inv.amount_tax) as tax_date_year_1, 0 as tax_year_1,
                        0 as tax_date_period,0 as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and inv.date_invoice <= '%s' and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (
            int(date.strftime('%Y')) - 1, date - relativedelta(years=1), company_id, currency['currency_id'])
            sql_date_year = ''' select cate.id, cate.name,sum(inv.amount_untaxed) as date_year, 0 as date_year_1, 0 as year_1,
                        0 as date_period,0 as date_period_1,0 as period_1, sum(inv.amount_tax) as tax_date_year, 0 as tax_date_year_1, 0 as tax_year_1,
                        0 as tax_date_period,0 as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and inv.date_invoice <= '%s' and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (date.strftime('%Y'), date, company_id, currency['currency_id'])
            sql_date_periods_1 = ''' select cate.id, cate.name,0 as date_year,0 as date_year_1, 0 as year_1,0 as date_period, 
                        sum(inv.amount_untaxed) as date_period_1,0 as period_1, 0 as  tax_date_year,0 as  tax_date_year_1, 0 as  tax_year_1,0 as  tax_date_period, 
                        sum(inv.amount_tax) as  tax_date_period_1,0 as  tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and date_part('month',inv.date_invoice)= %s 
                        and inv.date_invoice <= '%s'
                        and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (
            int(date.strftime('%Y')) - 1, date.strftime('%m'), date - relativedelta(years=1), company_id,
            currency['currency_id'])
            sql_date_periods = ''' select cate.id, cate.name,0 as date_year,0 as date_year_1, 0 as year_1, sum(inv.amount_untaxed) as date_period,
                        0 as date_period_1,0 as period_1, 0 as tax_date_year,0 as tax_date_year_1, 0 as tax_year_1, sum(inv.amount_tax) as tax_date_period,
                        0 as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and date_part('month',inv.date_invoice)= %s 
                        and inv.date_invoice <= '%s'
                        and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (date.strftime('%Y'), date.strftime('%m'), date, company_id, currency['currency_id'])
            sql_periods_1 = ''' select cate.id, cate.name,0 as date_year,0 as date_year_1, 0 as year_1,0 as date_period,0 as date_period_1, 
                        sum(inv.amount_untaxed) as period_1, 0 as tax_date_year,0 as tax_date_year_1, 0 as tax_year_1,0 as tax_date_period,0 as tax_date_period_1, 
                        sum(inv.amount_tax) as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and date_part('month',inv.date_invoice)= %s 
                        and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (int(date.strftime('%Y')) - 1, date.strftime('%m'), company_id, currency['currency_id'])
            sql = ''' select id, name, sum(date_year) as date_year, sum(date_year_1) as date_year_1, sum(year_1) as year_1,
                        sum(period)  as period,sum(period_1)  as period_1,sum(tax_date_year) as tax_date_year, sum(tax_date_year_1) as tax_date_year_1, 
                        sum(tax_year_1) as tax_year_1, sum(tax_period)  as tax_period,sum(tax_period_1)  as tax_period_1
                        from 
                        ((%s)  
                        union all (%s)
                        union all (%s)
                        union all (%s)
                        union all (%s)
                        union all (%s))as aa group by id, name ''' % (
            sql_year_1, sql_date_year_1, sql_date_year, sql_date_periods_1, sql_date_periods, sql_periods_1)

            self.cr.execute(sql)
            list_data = self.cr.dictfetchall()

            currency_name = u' [' + self.pool.get('res.currency').browse(self.cr, self.uid,
                                                                         currency['currency_id']).symbol + u']'
            year_1, date_year, date_year_1, date_month, date_month_1, month_1 = 0, 0, 0, 0, 0, 0
            for data in list_data:
                year_1 += data['year_1']
                date_year += data['date_year']
                date_year_1 += data['date_year_1']
                date_month += data['date_period']
                date_month_1 += data['date_period_1']
                month_1 += data['period_1']
                # add total tax for each company
                tax_year_1 += data['tax_year_1']
                tax_date_year += data['tax_date_year']
                tax_date_year_1 += data['tax_date_year_1']
                tax_date_month += data['tax_date_period']
                tax_date_month_1 += data['tax_date_period_1']
                tax_month_1 += data['tax_period_1']

                res = {
                    'name': data['name'] + currency_name,
                    'year_1': data['year_1'],
                    'date_year': data['date_year'],
                    'date_year_1': data['date_year_1'],
                    'date_month': data['date_period'],
                    'date_month_1': data['date_period_1'],
                    'month_1': data['period_1'],
                }

                result.append(res)
            if month or month_1:
                total_year_1 += currency_obj.compute(self.cr,
                                                     self.uid, currency['currency_id'], company_currency_id,
                                                     year_1, True, {'date': form['date']})
                total_date_year += currency_obj.compute(self.cr,
                                                        self.uid, currency['currency_id'], company_currency_id,
                                                        year, True, {'date_year': form['date_year']})
                total_date_year_1 += currency_obj.compute(self.cr,
                                                          self.uid, currency['currency_id'], company_currency_id,
                                                          year, True, {'date_year_1': form['date_year_1']})
                total_month_1 += currency_obj.compute(self.cr,
                                                      self.uid, currency['currency_id'], company_currency_id,
                                                      month_1, True, {'date': form['date']})
                total_date_month += currency_obj.compute(self.cr,
                                                         self.uid, currency['currency_id'], company_currency_id,
                                                         date_month, True, {'date': form['date']})
                total_date_month_1 += currency_obj.compute(self.cr,
                                                           self.uid, currency['currency_id'], company_currency_id,
                                                           date_month_1, True, {'date': form['date']})
                result.append({
                    'name': 'Total' + currency_name,
                    'year_1': year_1,
                    'date_year': date_year,
                    'date_year_1': date_year_1,
                    'date_month': date_month,
                    'date_month_1': date_month_1,
                    'month_1': month_1,
                })
        result.append({
            'name': 'Total Company' + u' ' + self.pool.get('res.company').browse(self.cr, self.uid, company_id).name,
            'year_1': total_year_1,
            'date_year': total_date_year,
            'date_year_1': total_date_year_1,
            'date_month': total_date_month,
            'date_month_1': total_date_month_1,
            'month_1': total_month_1,
        })
        self.total_year_1 += total_year_1
        self.total_date_year += total_date_year
        self.total_date_year_1 += total_date_year_1
        self.total_month_1 += total_month_1
        self.total_month += total_month
        return result

    def get_lines_group_by_category_partner(self, form, company_id):
        result = []
        res = {}
        currency_obj = self.pool.get('res.currency')
        invoice_obj = self.pool.get('account.invoice')
        date = datetime.datetime.strptime(form['date'], '%Y-%m-%d')
        sql = ''' select distinct(currency_id) as currency_id from account_invoice where date_part('year',date_invoice)<= %s and company_id = %s ''' % (
        date.strftime('%Y'), company_id)
        self.cr.execute(sql)
        list_currency = self.cr.dictfetchall()
        total_year_1, total_date_year, total_date_year_1, total_date_month, total_date_month_1, total_month_1 = 0, 0, 0, 0, 0, 0
        tax_total_year_1, tax_total_date_year, tax_total_date_year_1, tax_total_date_month, tax_total_date_month_1, tax_total_month_1 = 0, 0, 0, 0, 0, 0
        total_company_currency_id = self.pool.get('res.users').browse(self.cr, self.uid, self.uid,
            {}).company_id.currency_id.id
        company_currency_id = self.pool.get('res.company').browse(self.cr, self.uid, company_id, {}).currency_id.id
        for currency in list_currency:

            sql_year_1 = ''' select cate.id, cate.name, 0 as date_year,0 as date_year_1, sum(inv.amount_untaxed) as year_1,0 as date_period,0 as date_period_1,0 as period_1
                        , 0 as tax_date_year,0 as tax_date_year_1, sum(inv.amount_tax) as tax_year_1,0 as tax_date_period,0 as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (int(date.strftime('%Y')) - 1, company_id, currency['currency_id'])
            sql_date_year_1 = ''' select cate.id, cate.name, 0 as date_year, sum(inv.amount_untaxed) as date_year_1, 0 as year_1,0 as date_period,0 as date_period_1,0 as period_1
                                , 0 as tax_date_year, sum(inv.amount_tax) as tax_date_year_1, 0 as tax_year_1,0 as tax_date_period,0 as tax_date_period_1,0 as tax_period_1 
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and inv.date_invoice <= '%s' and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (
            int(date.strftime('%Y')) - 1, date - relativedelta(years=1), company_id, currency['currency_id'])
            sql_date_year = ''' select cate.id, cate.name,sum(inv.amount_untaxed) as date_year, 0 as date_year_1, 0 as year_1,0 as date_period,0 as date_period_1,0 as period_1
                            ,sum(inv.amount_tax) as tax_date_year, 0 as tax_date_year_1, 0 as tax_year_1,0 as tax_date_period,0 as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and inv.date_invoice <= '%s' and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (date.strftime('%Y'), date, company_id, currency['currency_id'])
            sql_date_periods_1 = ''' select cate.id, cate.name,0 as date_year,0 as date_year_1, 0 as year_1,0 as date_period, sum(inv.amount_untaxed) as date_period_1,0 as period_1
                            ,0 as tax_date_year,0 as tax_date_year_1, 0 as tax_year_1,0 as tax_date_period, sum(inv.amount_tax) as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and date_part('month',inv.date_invoice)= %s 
                        and inv.date_invoice <= '%s'
                        and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (
            int(date.strftime('%Y')) - 1, date.strftime('%m'), date - relativedelta(years=1), company_id,
            currency['currency_id'])
            sql_date_periods = ''' select cate.id, cate.name,0 as date_year,0 as date_year_1, 0 as year_1, sum(inv.amount_untaxed) as date_period,0 as date_period_1,0 as period_1
                            ,0 as tax_date_year,0 as tax_date_year_1, 0 as tax_year_1, sum(inv.amount_tax) as tax_date_period,0 as tax_date_period_1,0 as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and date_part('month',inv.date_invoice)= %s 
                        and inv.date_invoice <= '%s'
                        and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (date.strftime('%Y'), date.strftime('%m'), date, company_id, currency['currency_id'])
            sql_periods_1 = ''' select cate.id, cate.name,0 as date_year,0 as date_year_1, 0 as year_1,0 as date_period,0 as date_period_1, sum(inv.amount_untaxed) as period_1
                                ,0 as tax_date_year,0 as tax_date_year_1, 0 as tax_year_1,0 as tax_date_period,0 as tax_date_period_1, sum(inv.amount_tax) as tax_period_1
                        from account_invoice inv
                        inner join res_partner_category_rel rel on (inv.partner_id = rel.partner_id) 
                        inner join res_partner_category cate on (rel.category_id = cate.id)
                        where date_part('year',inv.date_invoice)= %s and date_part('month',inv.date_invoice)= %s 
                        and inv.company_id = %s and inv.currency_id = %s 
                        and inv.type = 'out_invoice' and inv.state not in ('draft','cancel') group by cate.id, cate.name
                        ''' % (int(date.strftime('%Y')) - 1, date.strftime('%m'), company_id, currency['currency_id'])
            sql = ''' select id, name, sum(date_year) as date_year, sum(date_year_1) as date_year_1, sum(year_1) as year_1,
                        sum(date_period_1)  as date_period_1,sum(date_period)  as date_period,sum(period_1)  as period_1, 
                        sum(tax_date_year) as tax_date_year, sum(tax_date_year_1) as tax_date_year_1, sum(tax_year_1) as tax_year_1,
                        sum(tax_date_period_1)  as tax_date_period_1,sum(tax_date_period)  as tax_date_period,sum(tax_period_1)  as tax_period_1 
                        from 
                        ((%s)  union all (%s)
                        union all (%s)
                        union all (%s)
                        union all (%s)
                        union all (%s))as aa group by id, name ''' % (
            sql_year_1, sql_date_year_1, sql_date_year, sql_date_periods_1, sql_date_periods, sql_periods_1)
            self.cr.execute(sql)
            list_data = self.cr.dictfetchall()

            currency_name = u' [' + self.pool.get('res.currency').browse(self.cr, self.uid,
                                                                         currency['currency_id']).symbol + u']'

            for data in list_data:
                if data['id'] not in res.keys():
                    res.update({data['id']: {
                        currency['currency_id']: {
                            'name': data['name'] + currency_name,
                            'categ': data['name'],
                            'currency': currency_name,
                            'year_1': data['year_1'],
                            'date_year': data['date_year'],
                            'date_year_1': data['date_year_1'],
                            'date_month': data['date_period'],
                            'date_month_1': data['date_period_1'],
                            'month_1': data['period_1'],
                            # add total tax
                            'tax_year_1': data['tax_year_1'],
                            'tax_date_year': data['tax_date_year'],
                            'tax_date_year_1': data['tax_date_year_1'],
                            'tax_date_month': data['tax_date_period'],
                            'tax_date_month_1': data['tax_date_period_1'],
                            'tax_month_1': data['tax_period_1'],
                        }}})
                else:
                    res[data['id']].update({currency['currency_id']: {
                        'name': data['name'] + currency_name,
                        'categ': data['name'],
                        'currency': currency_name,
                        'year_1': data['year_1'],
                        'date_year': data['date_year'],
                        'date_year_1': data['date_year_1'],
                        'date_month': data['date_period'],
                        'date_month_1': data['date_period_1'],
                        'month_1': data['period_1'],
                        # add total tax
                        'tax_year_1': data['tax_year_1'],
                        'tax_date_year': data['tax_date_year'],
                        'tax_date_year_1': data['tax_date_year_1'],
                        'tax_date_month': data['tax_date_period'],
                        'tax_date_month_1': data['tax_date_period_1'],
                        'tax_month_1': data['tax_period_1'],
                    }})
        dict_currency = {}

        for categ_id in res.keys():
            for currency_id in res[categ_id].keys():
                if company_currency_id in dict_currency.keys():
                    temp_year_1 = currency_obj.compute(self.cr,
                                                       self.uid, currency_id, company_currency_id,
                                                       res[categ_id][currency_id]['year_1'], True,
                                                       {'date': form['date']})
                    temp_date_year = currency_obj.compute(self.cr,
                                                          self.uid, currency_id, company_currency_id,
                                                          res[categ_id][currency_id]['date_year'], True,
                                                          {'date': form['date']})
                    temp_date_year_1 = currency_obj.compute(self.cr,
                                                            self.uid, currency_id, company_currency_id,
                                                            res[categ_id][currency_id]['date_year_1'], True,
                                                            {'date': form['date']})
                    temp_date_month = currency_obj.compute(self.cr,
                                                           self.uid, currency_id, company_currency_id,
                                                           res[categ_id][currency_id]['date_month'], True,
                                                           {'date': form['date']})
                    temp_date_month_1 = currency_obj.compute(self.cr,
                                                             self.uid, currency_id, company_currency_id,
                                                             res[categ_id][currency_id]['date_month_1'], True,
                                                             {'date': form['date']})
                    temp_month_1 = currency_obj.compute(self.cr,
                                                        self.uid, currency_id, company_currency_id,
                                                        res[categ_id][currency_id]['month_1'], True,
                                                        {'date': form['date']})
                    # add tax in report
                    tax_temp_year_1 = currency_obj.compute(self.cr,
                                                           self.uid, currency_id, company_currency_id,
                                                           res[categ_id][currency_id]['tax_year_1'], True,
                                                           {'date': form['date']})
                    tax_temp_date_year = currency_obj.compute(self.cr,
                                                              self.uid, currency_id, company_currency_id,
                                                              res[categ_id][currency_id]['tax_date_year'], True,
                                                              {'date': form['date']})
                    tax_temp_date_year_1 = currency_obj.compute(self.cr,
                                                                self.uid, currency_id, company_currency_id,
                                                                res[categ_id][currency_id]['tax_date_year_1'], True,
                                                                {'date': form['date']})
                    tax_temp_date_month = currency_obj.compute(self.cr,
                                                               self.uid, currency_id, company_currency_id,
                                                               res[categ_id][currency_id]['tax_date_month'], True,
                                                               {'date': form['date']})
                    tax_temp_date_month_1 = currency_obj.compute(self.cr,
                                                                 self.uid, currency_id, company_currency_id,
                                                                 res[categ_id][currency_id]['tax_date_month_1'], True,
                                                                 {'date': form['date']})
                    tax_temp_month_1 = currency_obj.compute(self.cr,
                                                            self.uid, currency_id, company_currency_id,
                                                            res[categ_id][currency_id]['tax_month_1'], True,
                                                            {'date': form['date']})

                    total_year_1 += temp_year_1
                    total_date_year += temp_date_year
                    total_date_year_1 += temp_date_year_1
                    total_date_month += temp_date_month
                    total_date_month_1 += temp_date_month_1
                    total_month_1 += temp_month_1
                    # add tax in report
                    tax_total_year_1 += tax_temp_year_1
                    tax_total_date_year += tax_temp_date_year
                    tax_total_date_year_1 += tax_temp_date_year_1
                    tax_total_date_month += tax_temp_date_month
                    tax_total_date_month_1 += tax_temp_date_month_1
                    tax_total_month_1 += tax_temp_month_1

                    if categ_id in dict_currency[company_currency_id].keys():
                        dict_currency[company_currency_id][categ_id]['year_1'] += temp_year_1
                        dict_currency[company_currency_id][categ_id]['date_year'] += temp_date_year
                        dict_currency[company_currency_id][categ_id]['date_year_1'] += temp_date_year_1
                        dict_currency[company_currency_id][categ_id]['date_month'] += temp_date_month
                        dict_currency[company_currency_id][categ_id]['date_month_1'] += temp_date_month_1
                        dict_currency[company_currency_id][categ_id]['month_1'] += temp_month_1
                        #add tax in report
                        dict_currency[company_currency_id][categ_id]['tax_year_1'] += tax_temp_year_1
                        dict_currency[company_currency_id][categ_id]['tax_date_year'] += tax_temp_date_year
                        dict_currency[company_currency_id][categ_id]['tax_date_year_1'] += tax_temp_date_year_1
                        dict_currency[company_currency_id][categ_id]['tax_date_month'] += tax_temp_date_month
                        dict_currency[company_currency_id][categ_id]['tax_date_month_1'] += tax_temp_date_month_1
                        dict_currency[company_currency_id][categ_id]['tax_month_1'] += tax_temp_month_1
                    #                            dict_currency[company_currency_id][categ_id]['name'] = '%s + %s'%(dict_currency[company_currency_id][categ_id]['name'],res[categ_id][currency_id]['currency'])
                    else:
                        dict_currency[company_currency_id].update({categ_id: {
                            'year_1': temp_year_1,
                            'date_year': temp_date_year,
                            'date_year_1': temp_date_year_1,
                            'date_month': temp_date_month,
                            'date_month_1': temp_date_month_1,
                            'month_1': temp_month_1,
                            'tax_year_1': tax_temp_year_1,
                            'tax_date_year': tax_temp_date_year,
                            'tax_date_year_1': tax_temp_date_year_1,
                            'tax_date_month': tax_temp_date_month,
                            'tax_date_month_1': tax_temp_date_month_1,
                            'tax_month_1': tax_temp_month_1,
                            'name': res[categ_id][currency_id]['categ'] + \
                                    u' [' + self.pool.get('res.currency').browse(self.cr, self.uid,
                                                                                 company_currency_id).symbol + u']',
                        }})
                else:
                    dict_currency.update({company_currency_id: {categ_id: {
                        'year_1': currency_obj.compute(self.cr,
                                                       self.uid, currency_id, company_currency_id,
                                                       res[categ_id][currency_id]['year_1'], True,
                                                       {'date': form['date']}),
                        'date_year': currency_obj.compute(self.cr,
                                                          self.uid, currency_id, company_currency_id,
                                                          res[categ_id][currency_id]['date_year'], True,
                                                          {'date': form['date']}),
                        'date_year_1': currency_obj.compute(self.cr,
                                                            self.uid, currency_id, company_currency_id,
                                                            res[categ_id][currency_id]['date_year_1'], True,
                                                            {'date': form['date']}),
                        'date_month': currency_obj.compute(self.cr,
                                                           self.uid, currency_id, company_currency_id,
                                                           res[categ_id][currency_id]['date_month'], True,
                                                           {'date': form['date']}),
                        'date_month_1': currency_obj.compute(self.cr,
                                                             self.uid, currency_id, company_currency_id,
                                                             res[categ_id][currency_id]['date_month_1'], True,
                                                             {'date': form['date']}),
                        'month_1': currency_obj.compute(self.cr,
                                                        self.uid, currency_id, company_currency_id,
                                                        res[categ_id][currency_id]['month_1'], True,
                                                        {'date': form['date']}),
                        # add tax
                        'tax_year_1': currency_obj.compute(self.cr,
                                                           self.uid, currency_id, company_currency_id,
                                                           res[categ_id][currency_id]['tax_year_1'], True,
                                                           {'date': form['date']}),
                        'tax_date_year': currency_obj.compute(self.cr,
                                                              self.uid, currency_id, company_currency_id,
                                                              res[categ_id][currency_id]['tax_date_year'], True,
                                                              {'date': form['date']}),
                        'tax_date_year_1': currency_obj.compute(self.cr,
                                                                self.uid, currency_id, company_currency_id,
                                                                res[categ_id][currency_id]['tax_date_year_1'], True,
                                                                {'date': form['date']}),
                        'tax_date_month': currency_obj.compute(self.cr,
                                                               self.uid, currency_id, company_currency_id,
                                                               res[categ_id][currency_id]['tax_date_month'], True,
                                                               {'date': form['date']}),
                        'tax_date_month_1': currency_obj.compute(self.cr,
                                                                 self.uid, currency_id, company_currency_id,
                                                                 res[categ_id][currency_id]['tax_date_month_1'], True,
                                                                 {'date': form['date']}),
                        'tax_month_1': currency_obj.compute(self.cr,
                                                            self.uid, currency_id, company_currency_id,
                                                            res[categ_id][currency_id]['tax_month_1'], True,
                                                            {'date': form['date']}),
                        'name': res[categ_id][currency_id]['categ'] + \
                                u' [' + self.pool.get('res.currency').browse(self.cr, self.uid,
                                                                             company_currency_id).symbol + u']',
                    }}
                    })

                    total_year_1 += dict_currency[company_currency_id][categ_id]['year_1']
                    total_date_year += dict_currency[company_currency_id][categ_id]['date_year']
                    total_date_year_1 += dict_currency[company_currency_id][categ_id]['date_year_1']
                    total_date_month += dict_currency[company_currency_id][categ_id]['date_month']
                    total_date_month_1 += dict_currency[company_currency_id][categ_id]['date_month_1']
                    total_month_1 += dict_currency[company_currency_id][categ_id]['month_1']
                    # add tax into report
                    tax_total_year_1 += dict_currency[company_currency_id][categ_id]['tax_year_1']
                    tax_total_date_year += dict_currency[company_currency_id][categ_id]['tax_date_year']
                    tax_total_date_year_1 += dict_currency[company_currency_id][categ_id]['tax_date_year_1']
                    tax_total_date_month += dict_currency[company_currency_id][categ_id]['tax_date_month']
                    tax_total_date_month_1 += dict_currency[company_currency_id][categ_id]['tax_date_month_1']
                    tax_total_month_1 += dict_currency[company_currency_id][categ_id]['tax_month_1']

        list_currency = sorted(dict_currency, key=lambda key: dict_currency[key])
        list_currency = list(set(list_currency))
        for key in list_currency:
            tax_1, year_tax, year_tax1, month_tax, month_tax1, tax = 0, 0, 0, 0, 0, 0
            currency_name = u' [' + self.pool.get('res.currency').browse(self.cr, self.uid, key).symbol + u']'
            for categ_id in dict_currency[key].keys():

                result.append({
                    'name': dict_currency[key][categ_id]['name'],
                    'year_1': dict_currency[key][categ_id]['year_1'],
                    'date_year': dict_currency[key][categ_id]['date_year'],
                    'date_year_1': dict_currency[key][categ_id]['date_year_1'],
                    'date_month': dict_currency[key][categ_id]['date_month'],
                    'date_month_1': dict_currency[key][categ_id]['date_month_1'],
                    'month_1': dict_currency[key][categ_id]['month_1'],
                })
                tax_1 += dict_currency[key][categ_id]['tax_year_1']
                year_tax += dict_currency[key][categ_id]['tax_date_year']
                year_tax1 += dict_currency[key][categ_id]['tax_date_year_1']
                month_tax += dict_currency[key][categ_id]['tax_date_month']
                month_tax1 += dict_currency[key][categ_id]['tax_date_month_1']
                tax += dict_currency[key][categ_id]['tax_month_1']
                # update total per categ
                if categ_id not in self.list_cate.keys():
                    self.list_cate.update({categ_id: {
                        'year_1': currency_obj.compute(self.cr,
                                                       self.uid, key, total_company_currency_id,
                                                       dict_currency[key][categ_id]['year_1'] +
                                                       dict_currency[key][categ_id]['tax_year_1'],
                                                       True, {'date': form['date']}),
                        'name': 'Total Group ' + self.pool.get('res.partner.category').browse(self.cr, self.uid,
                                                                                              categ_id).name,
                        'date_year': currency_obj.compute(self.cr,
                                                          self.uid, key, total_company_currency_id,
                                                          dict_currency[key][categ_id]['date_year'] +
                                                          dict_currency[key][categ_id]['tax_date_year'],
                                                          True, {'date': form['date']}),
                        'date_year_1': currency_obj.compute(self.cr,
                                                            self.uid, key, total_company_currency_id,
                                                            dict_currency[key][categ_id]['date_year_1'] +
                                                            dict_currency[key][categ_id]['tax_date_year_1'],
                                                            True, {'date': form['date']}),
                        'date_month': currency_obj.compute(self.cr,
                                                           self.uid, key, total_company_currency_id,
                                                           dict_currency[key][categ_id]['date_month'] +
                                                           dict_currency[key][categ_id]['tax_date_month'],
                                                           True, {'date': form['date']}),
                        'date_month_1': currency_obj.compute(self.cr,
                                                             self.uid, key, total_company_currency_id,
                                                             dict_currency[key][categ_id]['date_month_1'] +
                                                             dict_currency[key][categ_id]['tax_date_month_1'],
                                                             True, {'date': form['date']}),
                        'month_1': currency_obj.compute(self.cr,
                                                        self.uid, key, total_company_currency_id,
                                                        dict_currency[key][categ_id]['month_1'] +
                                                        dict_currency[key][categ_id]['tax_month_1'],
                                                        True, {'date': form['date']}),
                    }})
                else:

                    self.list_cate.update({categ_id: {
                        'year_1': self.list_cate[categ_id]['year_1'] + currency_obj.compute(self.cr,
                                                                                            self.uid, key,
                                                                                            total_company_currency_id,
                                                                                            dict_currency[key][
                                                                                                categ_id]['year_1'] +
                                                                                            dict_currency[key][
                                                                                                categ_id]['tax_year_1'],
                                                                                            True,
                                                                                            {'date': form['date']}),
                        'name': 'Total Group ' + self.pool.get('res.partner.category').browse(self.cr, self.uid,
                                                                                              categ_id).name + \
                                u' [' + self.pool.get('res.currency').browse(self.cr, self.uid,
                                                                             currency['currency_id']).symbol + u']',
                        'date_year': self.list_cate[categ_id]['date_year'] + currency_obj.compute(self.cr,
                                                                                                  self.uid, key,
                                                                                                  total_company_currency_id,
                                                                                                  dict_currency[key][
                                                                                                      categ_id][
                                                                                                      'date_year'] +
                                                                                                  dict_currency[key][
                                                                                                      categ_id][
                                                                                                      'tax_date_year'],
                                                                                                  True, {
                            'date': form['date']}),
                        'date_year_1': self.list_cate[categ_id]['date_year_1'] + currency_obj.compute(self.cr,
                                                                                                      self.uid, key,
                                                                                                      total_company_currency_id,
                                                                                                      dict_currency[
                                                                                                          key][
                                                                                                          categ_id][
                                                                                                          'date_year_1'] +
                                                                                                      dict_currency[
                                                                                                          key][
                                                                                                          categ_id][
                                                                                                          'tax_date_year_1'],
                                                                                                      True, {
                            'date': form['date']}),
                        'date_month': self.list_cate[categ_id]['date_month'] + currency_obj.compute(self.cr,
                                                                                                    self.uid, key,
                                                                                                    total_company_currency_id,
                                                                                                    dict_currency[key][
                                                                                                        categ_id][
                                                                                                        'date_month'] +
                                                                                                    dict_currency[key][
                                                                                                        categ_id][
                                                                                                        'tax_date_month'],
                                                                                                    True, {
                            'date': form['date']}),
                        'date_month_1': self.list_cate[categ_id]['date_month'] + currency_obj.compute(self.cr,
                                                                                                      self.uid, key,
                                                                                                      total_company_currency_id,
                                                                                                      dict_currency[
                                                                                                          key][
                                                                                                          categ_id][
                                                                                                          'date_month_1'] +
                                                                                                      dict_currency[
                                                                                                          key][
                                                                                                          categ_id][
                                                                                                          'tax_date_month_1'],
                                                                                                      True, {
                            'date': form['date']}),
                        'month_1': self.list_cate[categ_id]['month_1'] + currency_obj.compute(self.cr,
                                                                                              self.uid, key,
                                                                                              total_company_currency_id,
                                                                                              dict_currency[key][
                                                                                                  categ_id]['month_1'] +
                                                                                              dict_currency[key][
                                                                                                  categ_id][
                                                                                                  'tax_month_1'],
                                                                                              True,
                                                                                              {'date': form['date']}),
                    }})

            result.append({
                'name': 'TAX %s' % currency_name,
                'year_1': tax_1,
                'date_year': year_tax,
                'date_year_1': year_tax1,
                'date_month': month_tax,
                'date_month_1': month_tax1,
                'month_1': tax,
            })
        result.append({
            'name': 'Total Company' + u' ' + self.pool.get('res.company').browse(self.cr, self.uid, company_id).name,
            'year_1': total_year_1 + tax_total_year_1,
            'date_year': total_date_year + tax_total_date_year,
            'date_year_1': total_date_year_1 + tax_total_date_year_1,
            'date_month': total_date_month + tax_total_date_month,
            'date_month_1': total_date_month_1 + tax_total_date_month_1,
            'month_1': total_month_1 + tax_total_month_1,
        })

        self.total_year_1 += currency_obj.compute(self.cr,
                                                  self.uid, company_currency_id, total_company_currency_id,
                                                  total_year_1 + tax_total_year_1, True, {'date': form['date']})

        self.total_date_year += currency_obj.compute(self.cr,
                                                     self.uid, company_currency_id, total_company_currency_id,
                                                     total_date_year + tax_total_date_year, True,
                                                     {'date': form['date']})
        self.total_date_year_1 += currency_obj.compute(self.cr,
                                                       self.uid, company_currency_id, total_company_currency_id,
                                                       total_date_year_1 + tax_total_date_year_1, True,
                                                       {'date': form['date']})
        self.total_month_1 += currency_obj.compute(self.cr,
                                                   self.uid, company_currency_id, total_company_currency_id,
                                                   total_month_1 + tax_total_month_1, True, {'date': form['date']})
        self.total_date_month += currency_obj.compute(self.cr,
                                                      self.uid, company_currency_id, total_company_currency_id,
                                                      total_date_month + tax_total_date_month, True,
                                                      {'date': form['date']})
        self.total_date_month_1 += currency_obj.compute(self.cr,
                                                        self.uid, company_currency_id, total_company_currency_id,
                                                        total_date_month_1 + tax_total_date_month_1, True,
                                                        {'date': form['date']})
        return result
    
